require('dotenv').config();
const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

async function runMigration() {
  console.log('开始执行地理位置索引迁移...');
  
  // 创建数据库连接
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'help_u_save',
    multipleStatements: true // 允许执行多条SQL语句
  });
  
  try {
    // 读取迁移文件
    const migrationPath = path.join(__dirname, '../migrations/add_location_index_to_stores.sql');
    const migrationSQL = fs.readFileSync(migrationPath, 'utf8');
    
    console.log('读取迁移文件成功，开始执行SQL...');
    
    // 执行迁移
    const [results] = await connection.query(migrationSQL);
    
    console.log('迁移执行成功!');
    console.log('结果:', results);
    
    // 检查stores表中是否有地理位置数据
    const [rows] = await connection.query('SELECT COUNT(*) as count FROM stores WHERE latitude IS NOT NULL AND longitude IS NOT NULL');
    const count = rows[0].count;
    
    console.log(`当前有 ${count} 条店铺数据包含地理位置信息`);
    
    // 如果没有地理位置数据，添加一些测试数据
    if (count === 0) {
      console.log('没有找到地理位置数据，添加测试数据...');
      
      // 生成一些测试数据
      const testStores = [
        { name: '测试餐厅1', type: 'restaurant', address: '测试地址1', latitude: 30.5928, longitude: 114.3055, rating: 4.5, price_level: 2 },
        { name: '测试餐厅2', type: 'restaurant', address: '测试地址2', latitude: 30.5825, longitude: 114.2869, rating: 4.2, price_level: 3 },
        { name: '测试酒店1', type: 'hotel', address: '测试地址3', latitude: 30.6171, longitude: 114.2651, rating: 4.8, price_level: 3 },
        { name: '测试超市1', type: 'supermarket', address: '测试地址4', latitude: 30.6028, longitude: 114.3255, rating: 4.0, price_level: 1 },
        { name: '测试维修店', type: 'repair', address: '测试地址5', latitude: 30.5728, longitude: 114.2955, rating: 4.3, price_level: 2 },
        { name: '测试农产品店', type: 'agricultural', address: '测试地址6', latitude: 30.6128, longitude: 114.3155, rating: 4.6, price_level: 1 }
      ];
      
      // 插入测试数据
      for (const store of testStores) {
        await connection.query(
          `INSERT INTO stores (name, type, address, latitude, longitude, rating, price_level, is_approved) 
           VALUES (?, ?, ?, ?, ?, ?, ?, 1)`,
          [store.name, store.type, store.address, store.latitude, store.longitude, store.rating, store.price_level]
        );
        
        console.log(`添加测试店铺: ${store.name}`);
      }
      
      console.log('测试数据添加完成!');
    }
  } catch (error) {
    console.error('迁移执行失败:', error);
  } finally {
    // 关闭连接
    await connection.end();
    console.log('数据库连接已关闭');
  }
}

// 执行迁移
runMigration().catch(console.error); 